﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data.SqlClient;
using System.Data;
using System.Configuration;


namespace Common
{
    #region 实体类及枚举
    /// <summary>
    /// 简单数据类型
    /// </summary>
    public enum SmpDataType
    {
        unKnown,
        Double,
        String,
        DateTime
    }
    /// <summary>
    /// 数据库数据表的实体类
    /// </summary>
    public class TabProp
    {
        public int ID { get; set; }
        public string 数据表名称 { get; set; }
        public DateTime 建立时间 { get; set; }
        public DateTime 修改时间 { get; set; }
        public string 数据类型 { get; set; }
    }
    /// <summary>
    /// 数据库数据列的实体类
    /// </summary>
    public class ColProp
    {
        public int ID { get; set; }
        public int 数据表Id { get; set; }
        public string 数据列名 { get; set; }
        public string 数据类型 { get; set; }
        public int 长度 { get; set; }
        public int 精度 { get; set; }
        public int 小数位数 { get; set; }
    }
    public class PrimaryKey
    {
        public int 序号 { get; set; }
        public string 数据库名称 { get; set; }
        public string 数据表名称 { get; set; }
        public string 数据列名称 { get; set; }
        public string 主键名称 { get; set; }
        public string 数据表所有者 { get; set; }
    }
    /// <summary>
    /// 合并 DataTable 的参数实体类
    /// </summary>
    #endregion

    public class SqlHelper
    {
        public String ConnString { get; }
        public SqlHelper(string connString)
        {
            this.ConnString = connString;
        }

        #region 执行格式化的SQL语句

        /// <summary>
        /// 执行增、删、改（insert/update/delete）
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int Update(string sql)
        {
            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                try
                {
                    conn.Open();
                    int result = cmd.ExecuteNonQuery();
                    return result;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 执行单一结果查询（select）
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public object GetSingleResult(string sql)
        {
            SqlConnection conn = new SqlConnection(ConnString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                object result = cmd.ExecuteScalar();
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public object GetSingleResult(string sql, List<SqlParameter> parameterList)
        {
            SqlConnection conn = new SqlConnection(ConnString);
            SqlCommand cmd = new SqlCommand(sql, conn);

            try
            {
                conn.Open();
                foreach (SqlParameter item in parameterList)
                {
                    cmd.Parameters.AddWithValue(item.ParameterName, item.Value);
                }

                object result = cmd.ExecuteScalar();
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 执行多结果查询（select）
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public SqlDataReader GetReader(string sql)
        {
            SqlConnection conn = new SqlConnection(ConnString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                SqlDataReader objReader =
                    cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return objReader;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 执行返回数据集的查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql)
        {
            SqlConnection conn = new SqlConnection(ConnString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            SqlDataAdapter da = new SqlDataAdapter(cmd); //创建数据适配器对象
            DataTable dt = new DataTable();//创建一个内存数据表
            try
            {
                conn.Open();
                da.FillSchema(dt,SchemaType.Source );
                da.Fill(dt);  //使用数据适配器填充数据集
                return dt;  //返回数据集
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        #endregion

        #region 带参数的SQL语句

        public int Update(string sql, SqlParameter[] parameter)
        {
            SqlConnection conn = new SqlConnection(ConnString);
            SqlCommand cmd = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddRange(parameter);//为Command对象添加参数
                                                   //foreach (SqlParameter  item in parameter)
                                                   //{
                                                   //    cmd.Parameters.Add(item);
                                                   //}
                int result = cmd.ExecuteNonQuery();
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion

        #region 调用存储过程

        public int UpdateByProcedure(string procedureName, SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection(ConnString);
            SqlCommand cmd = new SqlCommand()
            {
                Connection = conn
            };
            try
            {
                conn.Open();
                cmd.CommandType = CommandType.StoredProcedure;//声明当前要执行的是存储过程
                cmd.CommandText = procedureName;//commandText只需要赋值存储过程名称即可
                cmd.Parameters.AddRange(param);//添加存储过程的参数
                int result = cmd.ExecuteNonQuery();
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 执行多结果查询（select）
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public SqlDataReader GetReaderByProcedure(string procedureName, SqlParameter[] param)
        {
            SqlConnection conn = new SqlConnection(ConnString);
            SqlCommand cmd = new SqlCommand()
            {
                Connection = conn
            };
            try
            {
                conn.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = procedureName;
                cmd.Parameters.AddRange(param);
                SqlDataReader objReader =
                    cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return objReader;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        #endregion

        #region 启用事务

        /// <summary>
        /// 启用事务执行多条SQL语句
        /// </summary>      
        /// <param name="sqlList">SQL语句列表</param>      
        /// <returns></returns>
        public bool ExecSQLByTran(List<string> sqlList)
        {
            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                SqlCommand cmd = new SqlCommand()
                {
                    Connection = conn
                };
                try
                {
                    conn.Open();
                    cmd.Transaction = conn.BeginTransaction();   //开启事务
                    foreach (string itemSql in sqlList)//循环提交SQL语句
                    {
                        cmd.CommandText = itemSql;
                        cmd.ExecuteNonQuery();
                    }
                    cmd.Transaction.Commit();  //提交事务(同时自动清除事务)
                    return true;
                }
                catch (Exception ex)
                {
                    if (cmd.Transaction != null)
                        cmd.Transaction.Rollback();//回滚事务(同时自动清除事务)
                    throw new Exception("调用事务方法时出现错误：" + ex.Message);
                }
                finally
                {
                    if (cmd.Transaction != null)
                        cmd.Transaction = null;
                    conn.Close();
                }
            }
        }

        #endregion

        #region 自行添加
        /// <summary>
        /// 获取数据库中所有数据表的属性
        /// </summary>
        /// <returns></returns>
        public List<TabProp> GetTableProp()
        {
            DataTable dt = GetDataTable("SELECT name, object_id,create_date, modify_date ,type FROM sys.objects WHERE (type in('u','v'))");
            List<TabProp> tabPropList = new List<Common.TabProp>();
            TabProp objTabProp = new TabProp();
            foreach (DataRow item in dt.Rows)
            {
                objTabProp = new Common.TabProp()
                {
                    ID = (int)item["object_id"],
                    数据表名称 = (string)item["name"],
                    建立时间 = (DateTime)item["create_date"],
                    修改时间 = (DateTime)item["modify_date"],
                    数据类型= (string)item["type"]
                };
                tabPropList.Add(objTabProp);
            }
            return tabPropList;
        }
        public List<ColProp> GetColPropByTabName(string tableName)
        {
            int tabId = GetTableProp().Where(s => s.数据表名称 == tableName).SingleOrDefault().ID;
            return GetColPropByTabID(tabId);
        }
        public List<ColProp> GetColPropByTabID(int tabID)
        {
            DataTable dt = GetDataTable($@"SELECT   sys.columns.object_id AS 数据表ID, sys.columns.column_id AS 数据列ID, sys.columns.name AS 数据列名, 
                    sys.types.name AS 数据类型, sys.columns.max_length AS 长度, sys.columns.precision as 精度, sys.types.scale AS 小数位数 
                    FROM sys.columns LEFT OUTER JOIN
                                    sys.types ON sys.columns.user_type_id = sys.types.user_type_id
                    WHERE(sys.columns.object_id = {tabID})");

            List<ColProp> colPropList = new List<Common.ColProp>();
            ColProp objColProp = new ColProp();
            foreach (DataRow item in dt.Rows)
            {
                objColProp = new ColProp()
                {
                    ID = (int)item["数据列ID"],
                    数据表Id = (int)item["数据表ID"],
                    数据列名 = (string)item["数据列名"],
                    数据类型 = (string)item["数据类型"],
                    长度 = (int)Convert.ToDouble(item["长度"]),
                    精度 = (int)Convert.ToDouble(item["精度"]),
                    小数位数 = (int)Convert.ToDouble(item["小数位数"])
                };
                colPropList.Add(objColProp);
            }
            return colPropList;
        }
        public List<PrimaryKey> GetPrimaryKey(string tableName)
        {
            List<PrimaryKey> resultList = new List<PrimaryKey>();

            SqlParameter[] parameter = new SqlParameter[] { new SqlParameter("@table_name", tableName) };
            SqlDataReader rd = GetReaderByProcedure("sp_pkeys", parameter);
            while (rd.Read())
            {
                resultList.Add(new PrimaryKey()
                {
                    序号 = Convert.ToInt32(rd["KEY_SEQ"]),
                    数据库名称 = rd["TABLE_QUALIFIER"].ToString(),
                    数据表名称 = rd["TABLE_NAME"].ToString(),
                    数据列名称 = rd["COLUMN_NAME"].ToString(),
                    主键名称 = rd["PK_NAME"].ToString(),
                    数据表所有者 = rd["TABLE_OWNER"].ToString()
                });
            }

            return resultList;
        }

        /// <summary>
        /// 获取数据类型的简单数据类型用于excel格式
        /// </summary>
        /// <param name="strDataType">数据类型字符串</param>
        /// <returns></returns>
        public SmpDataType GetSmpDataType(string strDataType)
        {
            switch (strDataType )
            {

                case "nvarchar":  return SmpDataType.String;
                case "nchar": return SmpDataType.String;

                case "money": return SmpDataType.Double ;
                case "int": return SmpDataType.Double;
                case "bigint": return SmpDataType.Double;
                case "decimal": return SmpDataType.Double;
                case "float": return SmpDataType.Double;
                case "real": return SmpDataType.Double;
                    
                case "date": return SmpDataType.DateTime ;
                case "datetime": return SmpDataType.DateTime ;

                default:return SmpDataType.unKnown ; 
            }
        }
        /// <summary>
        /// 根据输入数据表、包含数据库数据表的 Select sql 语句，更新数据库
        /// </summary>
        /// <param name="dtInput">输入数据表</param>
        /// <param name="selectCommand">Select sql 语句</param>
        /// <param name="conflictOption">更新方式选项，默认后来居上方式</param>
        /// <returns>返回受影响数据行的数量</returns>
        public int UpdateDataTableToSqlDbBySql(DataTable dtInput, string selectCommand, ConflictOption conflictOption = ConflictOption.OverwriteChanges)
        {
            SqlConnection cn = new SqlConnection(ConnString);
            SqlDataAdapter da = new SqlDataAdapter(selectCommand, cn);
            SqlCommandBuilder cb = new SqlCommandBuilder(da)
            {
                ConflictOption = conflictOption
            };
            da.InsertCommand = cb.GetInsertCommand(true);
            da.DeleteCommand = cb.GetDeleteCommand(true);
            da.UpdateCommand = cb.GetUpdateCommand(true);

            int intResult = 0;

            //删除
            if (dtInput.GetChanges(DataRowState.Deleted) != null)
            {
                try { intResult = intResult + da.Update(dtInput.GetChanges(DataRowState.Deleted)); }
                catch (Exception) { throw; }
            }

            //增加
            if (dtInput.GetChanges(DataRowState.Added) != null)
            {
                try { intResult = intResult + da.Update(dtInput.GetChanges(DataRowState.Added)); }
                catch (Exception) { throw; }
            }

            //修改
            if (dtInput.GetChanges(DataRowState.Modified) != null)
            {
                DataTable dtModDel = dtInput.GetChanges(DataRowState.Modified);
                DataTable dtModAdd = dtModDel.Copy();
                foreach (DataRow item in dtModDel.Rows)
                {
                    item.Delete();
                }
                try { da.Update(dtModDel); }
                catch (Exception) { throw; }

                foreach (DataRow item in dtModAdd.Rows)
                {
                    item.AcceptChanges();
                    item.SetAdded();
                }
                try { intResult = intResult + da.Update(dtModAdd); }
                catch (Exception) { throw; }
            }

            return intResult;
        }


        /// <summary>
        /// 根据输入数据表、及其唯一标识列名称列表；数据库字段名称列表、数据库数据表名称；是否全部更新、更新方式，来更新数据库
        /// </summary>
        /// <param name="dtInput">输入数据表</param>
        /// <param name="pkColumnName">输入数据表唯一标识列名称列表</param>
        /// <param name="dbDataTableName">数据库数据表名称</param>
        /// <param name="dbFieldNameList">数据库字段名称列表</param>
        /// <param name="isUpdateAll">是否更新全部，还是只是增加没有的</param>
        /// <param name="conflictOption">更新方式选项，默认后来居上方式</param>
        /// <returns>返回受影响数据行的数量</returns>
        public int UpdateDataTableToSqlDB(DataTable dtInput, List<string> pkColumnName, string dbDataTableName, List<string> dbFieldNameList, 
            bool isUpdateAll = false, ConflictOption conflictOption = ConflictOption.OverwriteChanges)
        {
            string strSelect = "";
            foreach (var item in dbFieldNameList)
            {
                strSelect = strSelect + $"{item},";
            }
            strSelect = strSelect.Substring(0, strSelect.Length - 1);
            strSelect = "select " + strSelect;
            strSelect = $"{strSelect } FROM [{dbDataTableName}]";

            DataTable dt = new DataTable();
            dt = MarkExistRow(dtInput, dbDataTableName, pkColumnName);

            foreach (DataRow item in dt.Rows)
            {
                if ((bool)item["IsExist"] == false)
                {
                    item.AcceptChanges();
                    item.SetAdded();
                }
                if ((bool)item["IsExist"] == true)
                {
                    if (isUpdateAll == true)
                    {
                        item.AcceptChanges();
                        item.SetModified();
                    }
                    else
                    {
                        item.AcceptChanges();
                    }
                }
            }

            return UpdateDataTableToSqlDbBySql(dt, strSelect, conflictOption);
        }

        /// <summary>
        /// 标识数据表在数据库中已存在的行
        /// </summary>
        /// <param name="inputTable">输入表</param>
        /// <param name="tableName">数据库中相应的表名</param>
        /// <param name="pkColumnName">唯一值的列名</param>
        /// <returns></returns>
        public DataTable MarkExistRow(DataTable inputTable, string tableName, List<string> pkColumnName)
        {
            DataTable dtResult = new DataTable();
            dtResult = inputTable;
            dtResult.Columns.Add("IsExist", typeof(bool));

            string sqlWhere = "";
            List<SqlParameter> sqlParaList = new List<SqlParameter>();

            foreach (var item in pkColumnName)
            {
                sqlWhere = sqlWhere + $" AND {item} = @{item}";
            }
            sqlWhere = sqlWhere.Substring(5, sqlWhere.Length - 5);
            string sql = $"SELECT COUNT(*) FROM {tableName} WHERE {sqlWhere}";

            List<SqlParameter> paraList = new List<SqlParameter>();
            int n;
            foreach (DataRow row in dtResult.Rows)
            {
                paraList = new List<SqlParameter>();
                foreach (var item in pkColumnName)
                {
                    SqlParameter objSqlPara = new SqlParameter($"@{item}", row[item]);
                    paraList.Add(objSqlPara);
                }
                n = (int)Convert.ToDouble(GetSingleResult(sql, paraList));
                if (n > 0) { row["IsExist"] = true; }
                else { row["IsExist"] = false; }
            }

            return dtResult;
        }

        /// <summary>
        /// 返回输入表中，在数据库中已存在的数据行的数据表
        /// </summary>
        /// <param name="inputTable">输入表</param>
        /// <param name="tableName">数据库中相应的表名</param>
        /// <param name="pkColumnName">唯一值的列名</param>
        /// <returns></returns>
        public DataTable GetTableExistDB(DataTable inputTable, string tableName, List<string> pkColumnName)
        {
            DataTable dt = new DataTable();
            dt = inputTable;

            DataRow[] rowFilter = MarkExistRow(dt, tableName, pkColumnName).Select("IsExist = true");
            DataTable dtResult = new DataTable();
            if (rowFilter.Count() > 0)
            {
                dtResult = rowFilter.CopyToDataTable();
            }

            return dtResult;
        }

        /// <summary>
        /// 返回输入表中，在数据库中不存在的数据行的数据表
        /// </summary>
        /// <param name="inputTable">输入表</param>
        /// <param name="tableName">数据库中相应的表名</param>
        /// <param name="pkColumnName">唯一值的列名</param>
        /// <returns></returns>
        public DataTable GetTableNotExistDB(DataTable inputTable, string tableName, List<string> pkColumnName)
        {
            DataTable dt = new DataTable();
            dt = inputTable;

            DataRow[] rowFilter = MarkExistRow(dt, tableName, pkColumnName).Select("IsExist = false");
            DataTable dtResult = new DataTable();
            if (rowFilter.Count() > 0)
            {
                dtResult = rowFilter.CopyToDataTable();
            }

            return dtResult;
        }


        #endregion
    }
}

